package download;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

class Info{
	int art_type;
	String art_title;
	String art_place;
	String art_sponsor;
	String art_actor;
	String art_duration;
	Date art_play_starttime;
	String art_price;
	Date art_sale_starttime;
	int art_sale_state;//'����״̬��-1�ѽ���0δ��ʼ��1��������', !!!2mearns error!
	String art_source;
	String art_description;
}

public class HallInfo {

	
	
	public static void writeInfo2Mysql(Info info) throws SQLException
	{
		Connection connection;
	    Statement statement;
	    ResultSet res;
	    //����Connector/J��
	    //��һ��Ҳ��дΪ��Class.forName("com.mysql.jdbc.Driver");
	    try {
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/lbs","liuyq", "haha");
		    if(connection != null)
		    {
		     System.out.println("连接成功！");
		    }
		    else
		    {
		    	System.out.println("连接失败！");
		    }
		    statement = connection.createStatement();  
		    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		  
		    String sql_search = "select * from activity_art_profile where art_source = '" +
		    		info.art_source +
		    		"';";
		    res = statement.executeQuery(sql_search);
		   
		    if(!res.next())
		    {
		    	System.out.println("No! Insert~");
		    	String sql_insert = "INSERT INTO  `lbs`.`activity_art_profile` (" +
		    			"`id` ," +
		    			"`art_type` ," +
		    			"`art_title` ," +
			    		"`art_place` ," +
			    		"`art_price` ," +
			    		"`art_sponsor` ," +
			    		"`art_actor` ," +
			    		"`art_duration` ," +
			    		"`art_play_starttime` ," +
			    		"`art_sale_starttime` ," +
			    		"`art_sale_state` ," +
			    		"`art_source` ," +
			    		"`update_time` ," +
			    		"`art_description`" +
			    		")" +
			    		"VALUES (NULL ,  '"
			    		+info.art_type+							"',  '"
			    		+info.art_title+						"',  '"
			    		+info.art_place+						"',  '"
			    		+info.art_price+						"',  '"
			    		+info.art_sponsor+						"',  '"
			    		+info.art_actor+						"',  '"
			    		+info.art_duration+						"',  '" 
			    		+sdf.format(info.art_play_starttime)+	"',  '"
			    		+sdf.format(info.art_sale_starttime)+	"',  '"
			    		+info.art_sale_state+					"',  '"
			    		+info.art_source+						"',	 '"
			    		+sdf.format(new java.util.Date())+		"' , '" 
			    		+"info.art_description"+"');";
		    	statement.execute(sql_insert);
		    }
		    else
		    {
		    	/*
		    	 * UPDATE  `lbs`.`activity_art_profile` SET  `art_title` =  'haha“2013国际迷你古典”系列音乐会之六 “英伦风范”英国钢琴家马求斯&#8226;伯劳维亚克 ',
`art_place` =  'haha蒙民伟音乐厅',
`art_sponsor` =  'haha清华大学艺术教育中心',
`art_actor` =  'haha吴氏策划' WHERE  `activity_art_profile`.`id` =201;
		    	 */
		    	String sql_update = "UPDATE  `lbs`.`activity_art_profile` SET " +
		    			"`art_type`= '"+info.art_type+
		    			"',`art_title`='" +info.art_title+
			    		"',`art_place`='"+ info.art_place+
			    		"',`art_sponsor`='"+ info.art_sponsor+
			    		"',`art_actor`='"+info.art_actor+
			    		"',`art_duration`='"+info.art_duration+
			    		"',`art_play_starttime`='"+sdf.format(info.art_play_starttime)+
			    		"',`art_price`= '" + info.art_price+
			    		"',`art_sale_starttime`= '"+sdf.format(info.art_sale_starttime)+
			    		"',`art_sale_state`='"+ info.art_sale_state+
			    		"',`art_source`='"+info.art_source+
			    		"',`update_time`='"+sdf.format(new java.util.Date())+
			    		"',`art_description`='" + "info.art_description"+
			    		"' WHERE `activity_art_profile`.`art_source`='"+
			    		info.art_source+"';";
		    	System.out.println(sql_update);
		    	statement.executeUpdate(sql_update);
		    	System.out.println("Yes! Update~");
		    }
		   
		    res.close();
		    statement.close();
		    connection.close();
		    
		}
	    catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	public static void getHallInfo() throws UnsupportedEncodingException, SQLException
	{
		String hall_page_GBK = CommonUtil.getHTML("http://www.hall.tsinghua.edu.cn/yczx.aspx", "GB2312");
		String hall_page = new String(CommonUtil.getUTF8BytesFromGBKString(hall_page_GBK), "UTF-8"); 
		Info info = new Info();
		String regexForm = "售票状态</td>\\s*</tr>([\\s\\S]*)<tr>\\s*<td colspan=";
		Pattern pat = Pattern.compile(regexForm);
		Matcher mat = pat.matcher(hall_page);
		//System.out.println(mat.matches());
		String allTrs="";
		if(mat.find())
		{
			allTrs = mat.group(1);
			regexForm = "<tr>([\\s\\S]+?)</tr>";
			Pattern patIn ;//= Pattern.compile(regex)
			Matcher matIn;
			Pattern patHref = Pattern.compile("href='([\\s\\S]+?)'");;
			Matcher matHref;
			pat = Pattern.compile(regexForm);
			mat = pat.matcher(allTrs);
			int index = 0;
			String string_date="";
			
			while(mat.find())
			{
				//System.out.println(mat.group(1));
				System.out.println("---------------------------------\n");
				
				patIn = Pattern.compile("(text3|white3)\">([\\s\\S]+?)</(td|a)>");
				matIn = patIn.matcher(mat.group(1));
				index = 0;
				while(matIn.find())
				{
					System.out.println(matIn.group(2));
					if(index==0) 
					{
						string_date = matIn.group(2);
					}
					else if(index==1)
					{
						string_date= string_date+" "+matIn.group(2)+":00";
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
						try {
							Date date = sdf.parse(string_date);
							info.art_play_starttime = date;
						} catch (ParseException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
					}
					else if(index==2)
					{
						info.art_place = matIn.group(2); 
					}
					else if(index==3)
					{
						info.art_title = matIn.group(2);
					}
					else if(index==4)
					{
						info.art_price = matIn.group(2);
					}
					else if(index==5)
					{//'����״̬��-1�ѽ���0δ��ʼ��1��������',
						System.out.println("!!!!!!!!!"+matIn.group(2));
						if(matIn.group(2).equals("结束"))
							info.art_sale_state = -1;
						else if(matIn.group(2).equals("待售票"))
							info.art_sale_state = 0;
						else if(matIn.group(2).equals("售票中"))
							info.art_sale_state = 1;
						else
							info.art_sale_state = 2;//2 means error!
					}
					else
					{
						System.out.println("soomething wrong! index is more than 5!!");
					}
					index++;
				}
				
				matHref = patHref.matcher(mat.group(1));
				if(matHref.find())
				{
					String link = "http://www.hall.tsinghua.edu.cn/"+matHref.group(1);
					System.out.println(link);
					info.art_source = link;
					String linkPage_GBK = CommonUtil.getHTML(link, "GB2312");
					String linkPage = new String(CommonUtil.getUTF8BytesFromGBKString(linkPage_GBK), "UTF-8"); 
					Pattern pat_link = Pattern.compile(">\\s*主办：([\\s\\S]+?)</span>");
					Matcher mat_link = pat_link.matcher(linkPage);
					if(mat_link.find())
					{
						System.out.println(mat_link.group(1));
						info.art_sponsor = mat_link.group(1);
					}
					
					pat_link = Pattern.compile(">\\s*演出：([\\s\\S]+?)</p>");
					mat_link = pat_link.matcher(linkPage);
					if(mat_link.find())
					{
						System.out.println(mat_link.group(1));
						info.art_actor = mat_link.group(1);
					}
					System.out.println("##################");
					pat_link = Pattern.compile(">\\s*售票时间：([\\s\\S]+?)<");
					mat_link = pat_link.matcher(linkPage);
					if(mat_link.find())
					{
						System.out.println(mat_link.group(1));
						Pattern pat_1 = Pattern.compile("\\s*月\\s*");
						String time = mat_link.group(1);
						time = "2013-"+time.trim()+":00";
						Matcher mat_1 = pat_1.matcher(time);
						time = mat_1.replaceAll("-");
						
						pat_1 = Pattern.compile("\\s*日\\s*");
						mat_1 = pat_1.matcher(time);
						time = mat_1.replaceAll(" ");
						
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
						try {
							Date date = sdf.parse(time);
							info.art_sale_starttime = date;
						} catch (ParseException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
					}
					
					/*pat_link = Pattern.compile("售票时间[\\s\\S]+?</p>([\\s\\S]+?)</table>");
					//String page = getHTML("http://www.hall.tsinghua.edu.cn/ycshow.aspx?id=249#", "GB2312");
					//System.out.println(page);
					mat_link = pat_link.matcher(linkPage);
					if(mat_link.find())
					{
						//System.out.println(mat.group(1));
						pat_link = Pattern.compile("(<[\\s\\S]+?>)|(&nbsp;)");
						mat_link = pat_link.matcher(mat_link.group(1));
						info.art_description = mat_link.replaceAll("");
						System.out.println(info.art_description);
					}*/
					Pattern pat_des = Pattern.compile("</table>[\\s\\S]*<table([\\s\\S]+?)剧目：");
					Matcher mat_des = pat_des.matcher(linkPage);
					String first = "<table ";
					//first = first+
					if(mat_des.find())
					{
						//System.out.println(mat.group(1));
						first = first+mat_des.group(1);
						pat_des = Pattern.compile("剧目：([\\s\\S]+?)</table>");
						mat_des = pat_des.matcher(linkPage);
						if(mat_des.find())
						{
							first = first+"剧目："+mat_des.group(1)+"</table>";
						}
						
						pat_des = Pattern.compile("<p>[\\s\\S]+?<img[\\s\\S]+?</p>");
						mat_des = pat_des.matcher(first);
						first = mat_des.replaceAll("");
						//System.out.println(first);
						info.art_description = first;
					}
				}
				info.art_type=1;
				writeInfo2Mysql(info);
			}
		}
		
	}
	/**
	 * 艺教中心的编码方式为GB2312
	 * @throws SQLException 
	 * @throws UnsupportedEncodingException 
	 */
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub

	}

}
